package com.learning.platform.repository;

import com.learning.platform.entity.Note;
import com.learning.platform.enums.NoteType;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 笔记数据访问接口
 */
@Repository
public interface NoteRepository extends JpaRepository<Note, Long> {
    
    /**
     * 根据用户ID和课程ID查找笔记
     */
    Page<Note> findByUserIdAndCourseIdOrderByCreatedAtDesc(Long userId, Long courseId, Pageable pageable);
    
    /**
     * 根据用户ID、课程ID和章节ID查找笔记
     */
    List<Note> findByUserIdAndCourseIdAndLessonIdOrderByCreatedAtDesc(Long userId, Long courseId, Long lessonId);
    
    /**
     * 根据用户ID查找所有笔记
     */
    Page<Note> findByUserIdOrderByCreatedAtDesc(Long userId, Pageable pageable);
    
    /**
     * 根据笔记类型查找笔记
     */
    List<Note> findByUserIdAndNoteTypeOrderByCreatedAtDesc(Long userId, NoteType noteType);
    
    /**
     * 查找视频时间点笔记
     */
    List<Note> findByUserIdAndLessonIdAndNoteTypeOrderByVideoTimestampAsc(
        Long userId, Long lessonId, NoteType noteType);
    
    /**
     * 根据标题或内容搜索笔记
     */
    @Query("SELECT n FROM Note n WHERE n.userId = :userId AND " +
           "(LOWER(n.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(n.content) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "ORDER BY n.createdAt DESC")
    Page<Note> searchByKeyword(@Param("userId") Long userId, 
                              @Param("keyword") String keyword, 
                              Pageable pageable);
    
    /**
     * 根据标签搜索笔记（使用原生SQL查询）
     */
    @Query(value = "SELECT * FROM notes n WHERE n.user_id = :userId AND " +
           "JSON_CONTAINS(n.tags, JSON_QUOTE(:tag)) " +
           "ORDER BY n.created_at DESC", nativeQuery = true)
    List<Note> findByUserIdAndTag(@Param("userId") Long userId, @Param("tag") String tag);
    
    /**
     * 统计用户在指定时间范围内创建的笔记数量
     */
    @Query("SELECT COUNT(n) FROM Note n WHERE n.userId = :userId AND " +
           "n.createdAt BETWEEN :startTime AND :endTime")
    long countByUserIdAndCreatedAtBetween(@Param("userId") Long userId,
                                         @Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime);
    
    /**
     * 获取用户最常用的标签
     */
    @Query(value = "SELECT tag, COUNT(*) as count FROM (" +
                  "SELECT JSON_UNQUOTE(JSON_EXTRACT(tags, CONCAT('$[', seq.seq - 1, ']'))) as tag " +
                  "FROM notes n " +
                  "CROSS JOIN (SELECT 1 as seq UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) seq " +
                  "WHERE n.user_id = :userId AND JSON_LENGTH(n.tags) >= seq.seq" +
                  ") as tag_list " +
                  "WHERE tag IS NOT NULL " +
                  "GROUP BY tag " +
                  "ORDER BY count DESC " +
                  "LIMIT :limit", nativeQuery = true)
    List<Object[]> getTopTagsByUserId(@Param("userId") Long userId, @Param("limit") int limit);
    
    /**
     * 获取用户在特定课程中的笔记统计
     */
    @Query("SELECT n.noteType, COUNT(n) FROM Note n WHERE n.userId = :userId AND n.courseId = :courseId " +
           "GROUP BY n.noteType")
    List<Object[]> getNoteStatsByCourse(@Param("userId") Long userId, @Param("courseId") Long courseId);
    
    /**
     * 删除课程的所有笔记
     */
    void deleteByCourseId(Long courseId);
    
    /**
     * 删除章节的所有笔记
     */
    void deleteByLessonId(Long lessonId);
    
    /**
     * 根据标题或内容搜索笔记（返回List）
     */
    @Query("SELECT n FROM Note n WHERE n.userId = :userId AND " +
           "(LOWER(n.title) LIKE LOWER(CONCAT('%', :titleKeyword, '%')) OR " +
           "LOWER(n.content) LIKE LOWER(CONCAT('%', :contentKeyword, '%'))) " +
           "ORDER BY n.createdAt DESC")
    List<Note> findByUserIdAndTitleContainingOrContentContaining(
        @Param("userId") Long userId, 
        @Param("titleKeyword") String titleKeyword, 
        @Param("contentKeyword") String contentKeyword);
}


